Release 10.1A: OpenEdge Development:
ProDataSets

Using a subset of the tables in a ProDataSet

You already know how to return an entire ProDataSet to another procedure. You also know how to deactivate relations or designate tables as NO-FILL. When you do this you return some tables as empty to the caller. Let’s look at how you could return a dynamic ProDataSet that represents a subset of the tables that are defined in a larger one. In the case of the code table ProDataSet, some callers might not want all the tables at all, or might not even know of their existence or have any definitions to receive them into. In this case the server can dynamically subset the data at the table level according to the caller’s request.

Let’s write that server procedure now. As with other examples, the code is simplified to the extent that it all in fact runs in a single session, but the backend server procedures are clearly separated from the user interface procedures that run on the client.

To update the code:

Create the new procedure called CodeSupport.p, as shown in the following code block.

It first includes the temp-table and ProDataSet definitions. The definitions, of course, also create an instance of that static ProDataSet and its temp-tables when CodeSupport.p is run. This means that in effect each run-time instance of CodeSupport.p “owns” its own instance of the ProDataSet as well. This allows any internal procedures to use static 4GL to reference the ProDataSet and its data, as well as any other procedures to which the ProDataSet might be passed by reference. This is different from how CodeSource.p operates. Because it has no static definition and receives only the ProDataSet handle as input, it operates only on an instance of the ProDataSet actually created and managed elsewhere (namely here in CodeSupport.p).

CodeSupport.p starts an instance of CodeSource.p and then requests it to attach the Data-Sources using the attachDataSet function and fills the ProDataSet, as shown:

/* CodeSupport.p -- support procedures for dsCode tables */ {dsCodeTT.i} {dsCode.i} DEFINE VARIABLE hSourceProc AS HANDLE NO-UNDO. DEFINE VARIABLE lError AS LOGICAL NO-UNDO. DEFINE VARIABLE hCodeSet AS HANDLE NO-UNDO. DEFINE VARIABLE hCodeSource AS HANDLE NO-UNDO. hCodeSet = DATASET dsCode:HANDLE. RUN CodeSource.p PERSISTENT SET hSourceProc. lError = DYNAMIC-FUNCTION ("attachDataSet" IN hSourceProc, hCodeSet). hCodeSet:FILL().

Next, create the internal procedure that actually generates a new ProDataSet with a subset of the tables in the original one, fetchCodeTables, as shown:

PROCEDURE fetchCodeTables: DEFINE INPUT PARAMETER pcTables AS CHARACTER NO-UNDO. DEFINE OUTPUT PARAMETER DATASET-HANDLE phDynData. DEFINE VARIABLE iTable AS INTEGER NO-UNDO. DEFINE VARIABLE cTable AS CHARACTER NO-UNDO. DEFINE VARIABLE hTableBuf AS HANDLE NO-UNDO. CREATE DATASET phDynData. DO iTable = 1 TO NUM-ENTRIES(pcTables): cTable = ENTRY(iTable,pcTables). CREATE BUFFER hTableBuf FOR TABLE cTable. phDynData:ADD-BUFFER(hTableBuf). END. END PROCEDURE. /* fetchCodeTables */

This takes a table list as input, creates a new dynamic ProDataSet along with new buffers for the static ProDataSet’s tables, and adds the buffers to the ProDataSet. This makes the existing data (already retrieved and filled) in the ProDataSet dsCode part of the new ProDataSet without any need to copy it, because the caller wants all the data in the requested subset of the tables.

Since you’re putting the same static temp-tables into the new ProDataSet, why do you need new dynamic buffers for them? Remember that there is a rule that a temp-table can be part of more than one ProDataSet at a time, but one temp-table buffer can only be part of one ProDataSet at a time. Progress generally manages the temp-tables in a ProDataSet through their buffers, and it can do this only when each ProDataSet has its own distinct set of buffers, even when temp-tables are shared. If you were to leave out the CREATE BUFFER statement, you could try to add the existing static buffer from dsCode directly into the new ProDataSet, as shown:

/* You can’t do this – you can’t use the same buffer in two ProDataSets: */ phDynData:ADD-BUFFER(DATASET dsCode:GET-BUFFER-HANDLE(iTable)).

If you did, you would get the following error when you run an application that uses fetchCodeTables:

Now build a user interface for the new ProDataSet.

To build the interface:

Create a new window procedure in the AppBuilder. Name it CodeWindow.w.
Make the window 10 rows by 130 columns.
Name the default frame CodeFrame and the window CodeWin.
Use the AppBuilder’s temp-table utility to define a temp-table ttState LIKE the State database table.
Drop a browse called StateBrowse onto the design window and attach it the ttState table and its three fields.

Your window should look roughly like this. Place the StateBrowse to leave room for other objects, as shown:



Add a statement to the Main Block to run a procedure where all the code will go to start up the window:

MAIN-BLOCK: DO ON ERROR UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK ON END-KEY UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK: RUN enable_UI. RUN startupCodeWindow. IF NOT THIS-PROCEDURE:PERSISTENT THEN WAIT-FOR CLOSE OF THIS-PROCEDURE. END.

Add a line to the CLOSE trigger in the Main Block for a procedure to shut down the window support code:

ON CLOSE OF THIS-PROCEDURE DO: RUN shutdownCodeWindow. RUN disable_UI. END.

Add these variables to the Definitions section:

DEFINE VARIABLE hCodeSupport AS HANDLE NO-UNDO. DEFINE VARIABLE hCodeSet AS HANDLE NO-UNDO. DEFINE VARIABLE hStateQuery AS HANDLE NO-UNDO. DEFINE VARIABLE hRepBrowse AS HANDLE NO-UNDO. DEFINE VARIABLE hRepQuery AS HANDLE NO-UNDO.

Create the internal procedure startupCodeWindow. This starts the CodeSupport procedure and then asks it for a ProDataSet with just the ttSalesRep and ttState tables in it, as shown:

/*--------------------------------------------------------------------- Procedure:   startupCodeWindow Purpose:     Fetch needed code tables from server. Parameters: <none> ---------------------------------------------------------------------*/ RUN codeSupport.p PERSISTENT SET hCodeSupport. RUN fetchCodeTables IN hCodeSupport (INPUT "ttSalesRep,ttState", OUTPUT DATASET-HANDLE hCodeSet).

Create a dynamic query for the ttState table that comes back as part of the dynamic ProDataSet, attaches it to the StateBrowse, prepares it, and opens it. The variables you use in this procedure are the ones you added in the Definitions section:

CREATE QUERY hStateQuery.    hStateQuery:ADD-BUFFER(hCodeSet:GET-BUFFER-HANDLE("ttState")).    StateBrowse:QUERY IN FRAME CodeFrame = hStateQuery.    hStateQuery:QUERY-PREPARE("FOR EACH ttState").    hStateQuery:QUERY-OPEN().

Again, you might ask why you need a new dynamic query for this table. After all, you just defined a static temp-table ttState and a static browse StateBrowse against that temp-table.

Once again, the answer is that you’re not really using that static temp-table. It only provides a definition to base the browse on. What comes back from fetchCodeTables is a separate dynamic temp-table that happens to have the same name and the same fields so that you can easily use it in place of the static temp-table you defined.

This means that you can’t simply open the static query and use it:

/* Can't do this: OPEN QUERY StateBrowse FOR EACH ttState. */

If you do, you won’t see any data in the browse.

To reinforce how to do this properly, you can create a dynamic query for the other table you’re getting back, ttSalesRep:

CREATE QUERY hRepQuery. hRepQuery:ADD-BUFFER(hCodeSet:GET-BUFFER-HANDLE("ttSalesRep")).

Be sure to add the right buffer to the query, which is the one for the temp-table that comes back as part of the dynamic ProDataSet hCodeSet. If you had a local definition of ttSalesRep, its buffer wouldn’t do you any good for the same reason that your local definition of ttState can’t be used.

This dynamic browse uses the ttSalesRep query:

CREATE BROWSE hRepBrowse ASSIGN QUERY = hRepQuery ROW-MARKERS = NO FRAME = FRAME CodeFrame:HANDLE HIDDEN = NO NO-VALIDATE = YES WIDTH = 74 HEIGHT = 5 ROW = 6 SEPARATORS = YES SENSITIVE = YES. hRepBrowse:ADD-COLUMNS-FROM("ttSalesRep").

Finally, the procedure needs to prepare and open the dynamic query on ttSalesRep:

hRepQuery:QUERY-PREPARE("FOR EACH ttSalesRep"). hRepQuery:QUERY-OPEN().

Now, when you run the window, you see both the static browse and the dynamic browse. Both are, in fact, using dynamic temp-tables that came back as part of the dynamic ProDataSet hCodeSet, as shown:



Define the internal procedure shutdownCodeWindow to delete the supporting procedure instance:

/*--------------------------------------------------------------------- Procedure: shutdownCodeWindow Purpose:   Cleanup supporting procedure and any other objects when            deleting            the window. ---------------------------------------------------------------------*/ APPLY "CLOSE" TO hCodeSupport. END PROCEDURE.

Rather than deleting it directly, applying the CLOSE event to it gives it a chance to clean up after itself. This is modeled on the standard code the AppBuilder generates to close a procedure by running disable_UI.

To handle the CLOSE event in CodeSupport.p, add this trigger to its main block so that it can delete the other persistent procedure CodeSource.p that manages the Data-Sources, and then delete itself:

ON CLOSE OF THIS-PROCEDURE DO: DYNAMIC-FUNCTION(“detachDataSet” IN hSourceProc, INPUT hCodeSet). DELETE PROCEDURE hSourceProc. DELETE PROCEDURE THIS-PROCEDURE. END.